﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using KPIS.GERP.GAMS.MODEL;
using KPIS.GERP.GAMS.DAL;
using Telerik.Web.UI;
using System.Collections.Generic;
using System.Globalization;
//Adding
using System.Data;
using System.Data.SqlClient;

namespace KPIS.GERP.GAMS.APP.App_UI.Forms
{
    public partial class ReportVatSale : System.Web.UI.Page
    {
        Commons.Main main = new Commons.Main();
        Commons.MailMerge mailMerge = new Commons.MailMerge();
        DateTime dStartDate = DateTime.Today;
        DateTime dEndDate = DateTime.Today;
        string sStartDate = DateTime.Today.ToLongDateString();
        string sEndDate = DateTime.Today.ToLongDateString();
        D_ACM_Finance_Recive Recive = new D_ACM_Finance_Recive();
        CultureInfo _thCulture = new CultureInfo("th-TH");
        D_BGM_MAS_Source source = new D_BGM_MAS_Source();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                UpdateUI1("Load");
                ddlBgmMonth.SelectedIndex = 0;
                txtBgmYear.Text = source.GetCurrentBgmYear();
            }
        }
        public void UpdateUI1(string strCommand)
        {

            switch (strCommand)
            {
                case "Load":



                    for (int i = 1; i <= 12; i++)
                    {

                        DateTime Tempdate = new DateTime(DateTime.Now.Year, i, 1);
                        RadComboBoxItem item = new RadComboBoxItem();
                        item.Text = Tempdate.ToString("MMMM", _thCulture);
                        item.Value = Convert.ToInt32(i).ToString();
                        ddlBgmMonth.Items.Add(item);

                        item.DataBind();
                    }

                    break;
            }
        }

        private void btnReport2_Click(object sender, EventArgs e)
        {
            string selectdate = ddlBgmMonth.SelectedValue; //เดือนที่เลือก
            string strMonth = ddlBgmMonth.Text;
            string strBgmYear = txtBgmYear.Text;
            LoadReport(selectdate, strBgmYear, strMonth);
        }

        private void LoadReport(string selectdate, string strBgmYear, string strMonth)
        {

            int? month = Convert.ToInt32(selectdate);

            int? year = Convert.ToInt32(strBgmYear);

            DataTable dt1 = ReportTaxSale(month, year);
            //DataTable dt2 = d_acm_general_ledger.GetBalanceSheet2(StartDate, EndDate);
            //DataTable dt3 = d_acm_general_ledger.GetBalanceSheet3(StartDate, EndDate);

            Dictionary<string, DataTable> dtbs = new Dictionary<string, DataTable>();

            dtbs.Add("Sale!A11", dt1);

            int sumRow = 11 + dt1.Rows.Count;
            double h = 0;
            double i = 0;
            double j = 0;
            double k = 0;

            foreach (DataRow r in dt1.Rows)
            {
                h += double.Parse(r["amount"].ToString());
                i += double.Parse(r["vatamount"].ToString());
                j += double.Parse(r["includevat"].ToString());
                k += double.Parse(r["a"].ToString());
            }

            //dtbs.Add("Debit!A6", dt2);
            //dtbs.Add("ShareHolder!A6", dt3);

            string strDate = "เดือนที่คิดภาษี" + " " + strMonth + " " + "ปี " + strBgmYear;
           Dictionary<string, string> selDate = new Dictionary<string, string>();
           selDate.Add("Sale!D3",strDate);
           selDate.Add(string.Format("Sale!G{0}", sumRow), "ยอดรวม ");
           selDate.Add(string.Format("Sale!H{0}",sumRow),h.ToString("#,##0.00"));
           selDate.Add(string.Format("Sale!I{0}", sumRow), i.ToString("#,##0.00"));
           selDate.Add(string.Format("Sale!J{0}", sumRow), j.ToString("#,##0.00"));
           selDate.Add(string.Format("Sale!K{0}", sumRow), k.ToString("#,##0.00"));

            //selDate.Add("Debit!D4", strDate);
           //selDate.Add("ShareHolder!D4", "เดือนที่คิดภาษี" + " " + strMonth + " " + "ปี " + strBgmYear);

           mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/VatSale.xlsx"), string.Format("VatSale{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), dtbs, selDate);
        }

        protected void btnReportx_Click(object sender, EventArgs e)
        {
            string selectdate = ddlBgmMonth.SelectedValue; //เดือนที่เลือก
            string strMonth = ddlBgmMonth.Text;
            string strBgmYear = txtBgmYear.Text;
            LoadReport(selectdate, strBgmYear, strMonth);
        }

        public DataTable ReportTaxSale(int? month, int? year)
        {
            int engYear = year.Value - 543;           
            DateTime dtFrom = new DateTime(engYear, month.Value, 1);
            DateTime dtTo = dtFrom.AddMonths(1).AddDays(-1);
            int lastDate = dtTo.Day;
            string startDate = string.Format("{0}-{1}-{2}",engYear,month,1);
            string endDate = string.Format("{0}-{1}-{2}", engYear, month, lastDate);

            string strConn = System.Web.Configuration.WebConfigurationManager.AppSettings["ConnectionString"];
            DataTable dt = new DataTable();

            string strSQL = string.Format("select ROW_NUMBER()OVER(ORDER BY a.receive_seq DESC) AS Row,a.receive_date as date,a.receipt_no as receiptNo,(case a.contact_seq when 0 then b.NameTh else a.temp_contact end) as ConName,(b.TaxCode)as CodeCon,'-' as HQName,'-' as BranchName,(case a.vat_amount when 0 then 0 else a.amount-a.vat_amount end) as amount ,a.vat_amount as vatamount,( case a.vat_amount when 0 then 0 else a.amount END ) as IncludeVat,(case a.vat_amount when  0   then a.amount  else 0 END) as a"
                                         +" from fim_receives a"
		                                 +" left join fim_vat_dtls vd on a.pay_seq = vd.pay_seq"
		                                 +" left join fim_vat v on vd.vat_seq = v.vat_seq and v.record_status <> 'D'"
		                                 +" left join IcmMasContact b on a.contact_seq=b.ConId"
                                         +" where coalesce(a.vat_amount, 0) > 0 and a.bgm_source_seq = 11"
		                                 +" and a.receive_date between '{0}' and '{1}'"
		                                 +" and (vd.vat_seq is null)",startDate,endDate);

            using (SqlConnection conn = new SqlConnection(strConn))
            {
                using (SqlDataAdapter adp = new SqlDataAdapter(strSQL, conn))
                {
                    adp.Fill(dt);
                }
            }

            return dt;
        }

    }
}